IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[famSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[famSelect]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[famSelectAll]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[famSelectAll]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[famCreate]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[famCreate]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[famDelete]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[famDelete]
GO
CREATE PROC [dbo].[famSelect] @FamilyId int, @PFamilyId int, @FamilyName nvarchar(50), @Mother nvarchar(50), @Father nvarchar(50), @Address nvarchar(100), @City nvarchar(50), @State nvarchar(2), @Zip nvarchar(5), @Phone1 nvarchar(12), @Phone2 nvarchar(12), @MemberCount int
AS
SELECT * FROM [dbo].[Families] WHERE [FamilyId]=@FamilyId AND [PFamilyId]=@PFamilyId AND [FamilyName]=@FamilyName AND [Mother]=@Mother AND [Father]=@Father AND [Address]=@Address AND [City]=@City AND [State]=@State AND [Zip]=@Zip AND [Phone1]=@Phone1 AND [Phone2]=@Phone2 AND [MemberCount]=@MemberCount
GO
CREATE PROC [dbo].[famDelete] @FamilyId int, @PFamilyId int, @FamilyName nvarchar(50), @Mother nvarchar(50), @Father nvarchar(50), @Address nvarchar(100), @City nvarchar(50), @State nvarchar(2), @Zip nvarchar(5), @Phone1 nvarchar(12), @Phone2 nvarchar(12), @MemberCount int
AS
DELETE FROM [dbo].[Families] WHERE [FamilyId]=@FamilyId AND [PFamilyId]=@PFamilyId AND [FamilyName]=@FamilyName AND [Mother]=@Mother AND [Father]=@Father AND [Address]=@Address AND [City]=@City AND [State]=@State AND [Zip]=@Zip AND [Phone1]=@Phone1 AND [Phone2]=@Phone2 AND [MemberCount]=@MemberCount
GO
CREATE PROC [dbo].[famSelectAll]
AS
SELECT * FROM [dbo].[Families]
GO
CREATE PROC [dbo].[famCreate] @FamilyId int, @PFamilyId int, @FamilyName nvarchar(50), @Mother nvarchar(50), @Father nvarchar(50), @Address nvarchar(100), @City nvarchar(50), @State nvarchar(2), @Zip nvarchar(5), @Phone1 nvarchar(12), @Phone2 nvarchar(12), @MemberCount int
AS
IF EXISTS (SELECT * FROM [dbo].[Families] WHERE [FamilyId]=@FamilyId AND [PFamilyId]=@PFamilyId AND [FamilyName]=@FamilyName AND [Mother]=@Mother AND [Father]=@Father AND [Address]=@Address AND [City]=@City AND [State]=@State AND [Zip]=@Zip AND [Phone1]=@Phone1 AND [Phone2]=@Phone2 AND [MemberCount]=@MemberCount)
BEGIN
	--update existing record
	UPDATE [dbo].[Families] SET [FamilyId]=@FamilyId, [PFamilyId]=@PFamilyId, [FamilyName]=@FamilyName, [Mother]=@Mother, [Father]=@Father, [Address]=@Address, [City]=@City, [State]=@State, [Zip]=@Zip, [Phone1]=@Phone1, [Phone2]=@Phone2, [MemberCount]=@MemberCount
	WHERE [FamilyId]=@FamilyId AND [PFamilyId]=@PFamilyId AND [FamilyName]=@FamilyName AND [Mother]=@Mother AND [Father]=@Father AND [Address]=@Address AND [City]=@City AND [State]=@State AND [Zip]=@Zip AND [Phone1]=@Phone1 AND [Phone2]=@Phone2 AND [MemberCount]=@MemberCount
END
ELSE
BEGIN
	--insert new record
	INSERT INTO [dbo].[Families] ([FamilyId], [PFamilyId], [FamilyName], [Mother], [Father], [Address], [City], [State], [Zip], [Phone1], [Phone2], [MemberCount])
	VALUES (@FamilyId, @PFamilyId, @FamilyName, @Mother, @Father, @Address, @City, @State, @Zip, @Phone1, @Phone2, @MemberCount)
	
END

SELECT * FROM [dbo].[Families] WHERE [FamilyId]=@FamilyId AND [PFamilyId]=@PFamilyId AND [FamilyName]=@FamilyName AND [Mother]=@Mother AND [Father]=@Father AND [Address]=@Address AND [City]=@City AND [State]=@State AND [Zip]=@Zip AND [Phone1]=@Phone1 AND [Phone2]=@Phone2 AND [MemberCount]=@MemberCount
GO
